﻿using System.Configuration;
using System;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace ImageProcessing.Utility
{
    public class Database
    {
        private string _connection;
        public static Database Open(string dbname)
        {
            Database db = new Database();

            db._connection = ConfigurationManager.ConnectionStrings[dbname].ConnectionString;
            return db;
        }

        public bool InsertUpdateImage(string filename, string suffix, string folder)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(_connection))
                {
                    connection.Open();
                    using (SqlCommand cmd = new SqlCommand("dbo.sp_goods_id_suffix_steven",connection))
                    {
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@goods_id", filename);
                        cmd.Parameters.AddWithValue("@folder_name", folder);
                        cmd.Parameters.AddWithValue("@suffix", suffix);

                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }

        public bool UpdateImagesCameraModelAndResolution(int width, int height, string cameraModel, string goodId, string suffix)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(_connection))
                {
                    connection.Open();
                    string strCmd = "Update images set resolution = '{0}x{1}', camera_model = '{2}' where goodslid = '{3}' and suffix = '{4}'";
                    strCmd = string.Format(strCmd, width, height, cameraModel, goodId, suffix);
                    using (SqlCommand cmd = new SqlCommand(strCmd,connection))
                    {
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception)
            {
                return false;
            }
            return true;
        }

        public List<PhotoInfo> SearchImagesByGoodID(string goodID)
        {
            List<PhotoInfo> infos = new List<PhotoInfo>();
            try
            {
                using (SqlConnection connection = new SqlConnection(_connection))
                {
                    connection.Open();

                    string cmd = "SELECT goodslid, suffix, folder from dbo.images where goodslid = " + goodID;
                    using (SqlCommand sqlCmd = new SqlCommand(cmd, connection))
                    {
                        SqlDataReader reader = sqlCmd.ExecuteReader();
                        while (reader.Read())
                        {
                            PhotoInfo info = new PhotoInfo();
                            info.GoodID = reader["goodslid"].ToString();
                            info.Suffix = reader["suffix"].ToString();
                            info.MD5Folder = reader["folder"].ToString();
                            infos.Add(info);
                        }
                    }
                }
            }
            catch (Exception)
            {
                return null;
            }
            return infos;

        }

        public PhotoInfo CheckPhotoExist(string goodId, string suffix)
        {
                            
            PhotoInfo info = new PhotoInfo();
            try
            {
                using (SqlConnection connection = new SqlConnection(_connection))
                {
                    connection.Open();

                    string cmd = string.Format("SELECT goodslid, suffix, folder,modtime from dbo.images where goodslid = '{0}' and suffix = '{1}' ", goodId,suffix);
                    using (SqlCommand sqlCmd = new SqlCommand(cmd, connection))
                    {
                        SqlDataReader reader = sqlCmd.ExecuteReader();
                        while (reader.Read())
                        {
                            info.GoodID = reader["goodslid"].ToString();
                            info.Suffix = reader["suffix"].ToString();
                            info.MD5Folder = reader["folder"].ToString();
                            info.LoadTime = DateTime.Parse(reader["modtime"].ToString());
                        }
                    }
                }
            }
            catch (Exception)
            {
                return null;
            }
            return info;
        }

    }
}